﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

namespace PropertyManagement.zsyy
{
    public partial class addZSExcel : System.Web.UI.Page
    {
        string[] arrSheet = { "zsnews" };
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                addCheckBoxList();
            }
            Label2.Visible = false;
            Label2.Text = "";
        }

        public void addCheckBoxList()
        {
            for (int i = 0; i < arrSheet.Length; i++)
            {
                ListItem item = new ListItem(arrSheet[i]);
                item.Attributes.Add("style", "width:100px;height:24px;");
                CheckBoxList1.Items.Add(item);
            }
            CheckBoxList1.RepeatDirection = 0;
            CheckBoxList1.RepeatColumns = 4;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            MyExcel.Application excel = new MyExcel.Application();
            string sExcelFile = TextBox1.Text;
            FileInfo fileInfo = new FileInfo(sExcelFile);
            if (!fileInfo.Exists)
            {
                Label2.Visible = true;
                Label2.Text = "Excel文件名不存在";
                return;
            }

            object missing = Missing.Value;
            MyExcel.Workbook workbook = excel.Application.Workbooks.Open(sExcelFile, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, missing, missing,
                missing, missing, missing);
            excel.Visible = true;

            MyExcel.Worksheet sheet = workbook.Worksheets["Sheet1"];
            int n1 = addDataToTable(sheet);

            if (n1 > 0)
            {
                Label2.Text = "excel添加数据成功";
                Label2.Visible = true;
            }
            else
            {
                Label2.Text = "excel添加数据失败";
                Label2.Visible = true;
            }
            excel.Application.DisplayAlerts = false;
            workbook.Close();
            excel.Quit();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            int m1;
            string strsql = "delete from suser";
            mydata myda = new mydata();
            m1 = myda.execSql(strsql);

            if (m1 > 0)
            {
                Label2.Text = "删除数据成功";
                Label2.Visible = true;
            }
            else
            {
                Label2.Text = "删除数据失败";
                Label2.Visible = true;
            }
        }

        public int addDataToTable(MyExcel.Worksheet sheet)
        {
            int n1 = -1;
            MyExcel.Range usedRange = sheet.UsedRange;

            object[,] arrItem = usedRange.Value2;
            string title = "";
            string fbdate, person, content, picture1;

            string[] arrStrSql;
            string strsql = "insert into zsnews(title,fbdate,person,content,picture1) values(";
            string strsql2 = "";
            int k = 0;

            if (usedRange.Cells.Rows.Count >= 2)
            {
                arrStrSql = new string[usedRange.Cells.Rows.Count];

                for (int i = 2; i < usedRange.Cells.Rows.Count + 1; i++)
                {
                    title = arrItem[i, 1].ToString();
                    fbdate = arrItem[i, 2].ToString();
                    person = arrItem[i, 3].ToString();
                    content = arrItem[i, 4].ToString();
                    picture1 = arrItem[i, 5].ToString();

                    strsql2 = strsql
                        + "'" + title + "',"
                        + "'" + fbdate + "',"
                        + "'" + person + "',"
                        + "'" + content + "',"
                        + "'" + picture1 + "'"
                        + ")";

                    arrStrSql[k] = strsql2;
                    k++;
                }
                mydata myda = new mydata();
                n1 = myda.MultiExecSql(arrStrSql);
            }
            return n1;
        }

    }
}